-- @owner: songjing20
-- @date: 2022-6-29
-- @testpoint: LIMIT语法梳理
-- @modified by: xiaqi14 2024/12/9 研发代码变更,修改时间精度

--step1:创建ustore表;expect:创建表样成功
drop table if exists t_limit_clause_0001;
create table t_limit_clause_0001(
COL_1 int, 
COL_2 decimal,
COL_3 smallint,
COL_4 char(30),
COL_5 real,
COL_6 numeric,
COL_7 int,
COL_8 TIMESTAMP,
COL_9 clob,
COL_10 number(6,2),
COL_11 decimal(6,2),
COL_12 varchar2(50),
COL_13 varchar(30),
COL_14 numeric(12,6),
COL_15 date,
constraint pk_limit_id primary key(COL_1)
) WITH (STORAGE_TYPE=USTORE);

--step2:创建序列给表插入数据;expect:插入数据成功
drop sequence if exists seq_limit_clause_0001;
create sequence seq_limit_clause_0001 increment by 1 start with 100000;
begin
 for i in 1..100 loop
      insert into t_limit_clause_0001 values(
      seq_limit_clause_0001.nextval,
      3.1415926+seq_limit_clause_0001.nextval,
      i,
      lpad('abc','30','@'),
      3.1415926+seq_limit_clause_0001.nextval,
      i/4,
      i,
      to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FF'),
      lpad('abc','5000','a@123&^%djgk'),
      3.14+i,
      i+445.255,
      rpad('abc','30','&'),
      lpad('abc','30','&'),
      125563.141592,
      to_date('2019-03-1 14:58:54','YYYY-MM-DD HH24:MI:SS')
   );
 end loop;
end;
/

--step3:查询表样中10-20的数据;expect:查询成功
select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0001
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 limit 10,20;

--step4:查询表样中前20行的数据;expect:查询成功
select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0001
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 limit 20;

--step5:跳过表样前10行数据取20行数据;expect:查询成功
select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0001
order by COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 limit 20 OFFSET 10;

--step6:跳过表样前10行数据取20行数据;expect:查询成功
select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0001
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 OFFSET 10 limit 20;

--step7:清理环境;expect:成功
drop table if exists t_limit_clause_0001 cascade;
drop sequence if exists seq_limit_clause_0001;
